﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CoreApi.SQL
{
    public static class TABLE
    {
        public static string ACCOUNT = "Account";
        public static string CITIZEN = "SeniorCitizen";
        public static string DEVICE = "Device";
        public static string DEVICEDATA = "DeviceData";
        public static string BLOODPRESSURE = "BloodPressureMeasure";
        public static string GLYCEMIC = "GlycemicMeasure";
        public static string SMOKEALARM = "SmokeAlarmRecord";
        public static string ACCOUNTALARM = "AccountAlarm";
        public static string PROPERTYMANAGER = "PropertyManager";
        public static string SERVICESHEET="ServiceSheet";
        public static string SERVICESHEETOPT="ServiceSheetOptLog";
        public static string MEMBER = "FamilyMember";
        public static string ACCOUNTMEMBER="AccountMember";
        public static string BRAND="DeviceBrand";
        public static string MODEL = "DeviceModel";
        public static string GASALARM = "GasAlarmRecord";
        public static string RW_BP = "RW_BP";
        public static string RW_GL = "RW_GL";
        public static string RW_SA = "RW_SA";
        public static string RW_GA = "RW_GA";
    }

    public static class DataCollect
    {
        public static string FindCitizen = string.Format("select a.AccountId,a.CitizenId,a.Gender,a.BirthdayYear from {0} a inner join {1} b on a.CitizenId=b.CitizenId inner join {2} c on b.DeviceId=c.DeviceId", SQL.TABLE.CITIZEN, SQL.TABLE.DEVICEDATA, SQL.TABLE.DEVICE);
        public static string FindAccount = string.Format("select a.AccountId from {0} a inner join {1} b on a.DeviceId=b.DeviceId", SQL.TABLE.DEVICEDATA, SQL.TABLE.DEVICE);
    }

    public static class PropertyManagerService
    {
        public static string PropertyRegisterMobile = string.Format("select count(a.ManagerId) from {0} a", SQL.TABLE.PROPERTYMANAGER);
        public static string GetServiceSheet = string.Format("select a.ServiceSheetId,a.AccountId,a.ManagerId,a.AlarmId,a.Status,a.CreateBy,a.CreateTime,a.ClosedBy,a.ClosedTime,b.AccountId,b.CitizenId,b.Type,b.MeasureTime,b.MeasureValue,b.Sbp,b.Dbp,b.Description,b.Attribute,b.AlarmTime,c.Name,c.Gender,c.BirthdayYear,c.ContactPhone,c.ContactMobile,d.Road,d.LaneNumber,d.ContactPhone AccountContactPhone from {0} a left join {1} b on a.AlarmId=b.AlarmId left join {2} c on b.CitizenId=c.CitizenId inner join {3} d on b.AccountId=d.AccountId inner join {4} e on a.ManagerId=e.ManagerId", SQL.TABLE.SERVICESHEET, SQL.TABLE.ACCOUNTALARM, SQL.TABLE.CITIZEN,SQL.TABLE.ACCOUNT,SQL.TABLE.PROPERTYMANAGER);
        public static string GetServiceSheetOpt = string.Format("select a.LogId,a.ServiceSheetId,a.OptResult,a.OptName,a.OptTime,a.Comments from {0} a inner join {1} b on a.ServiceSheetId=b.ServiceSheetId left join {2} c on b.ManagerId=c.ManagerId", SQL.TABLE.SERVICESHEETOPT, SQL.TABLE.SERVICESHEET, TABLE.PROPERTYMANAGER);
    }

    public static class UserService
    {
        public static string MemberRegisterMobile = string.Format("select count(a.MemberId) from {0} a", SQL.TABLE.MEMBER);
        public static string AccountList = string.Format("select b.AccountId,c.Province,c.City,c.District,c.Address from {0} a inner join {1} b on a.MemberId=b.MemberId inner join {2} c", SQL.TABLE.MEMBER, SQL.TABLE.ACCOUNTMEMBER, SQL.TABLE.ACCOUNT);
        public static string FamilyMemberList = string.Format("select a.MemberId,a.RealName,a.RegMobile,a.OtherPhone,a.Province,a.City,a.District,a.Address,a.PostCode,b.CanManagement from {0} a inner join {1} b on a.MemberId=b.MemberId where b.AccountId=(select c.AccountId from {2} c inner join {3} d on c.MemberId=d.MemberId [...])", SQL.TABLE.MEMBER, SQL.TABLE.ACCOUNTMEMBER, SQL.TABLE.ACCOUNTMEMBER, SQL.TABLE.MEMBER);
        public static string SeniorCitizenList = string.Format("select a.CitizenId,a.Name,a.BirthdayYear,a.Gender,a.ContactMobile,a.IdCard,a.Height,a.Weight,a.NativePlace from {0} a inner join {1} b on a.AccountId=b.AccountId inner join {2} c on b.MemberId=c.MemberId", SQL.TABLE.CITIZEN, SQL.TABLE.ACCOUNTMEMBER, SQL.TABLE.MEMBER);
        public static string DeviceList = string.Format("select a.DeviceId,a.DeviceType,a.BrandId,b.Name BrandName,a.Model,a.DeviceSN,a.SimSN from {0} a left join {1} b on a.BrandId=b.BrandId inner join {2} c on a.AccountId=c.AccountId inner join {3} d on c.MemberId=d.MemberId", SQL.TABLE.DEVICE, SQL.TABLE.BRAND, SQL.TABLE.ACCOUNTMEMBER, SQL.TABLE.MEMBER);
        public static string AlarmList = string.Format("select a.AlarmId,a.AccountId,a.CitizenId,d.Name olderName,a.Type,a.MeasureTime,a.MeasureValue,a.Sbp,a.Dbp,a.Attribute,a.Description,a.AlarmTime from {0} a inner join {1} b on a.AccountId=b.AccountId inner join {2} c on b.MemberId = c.MemberId left join {3} d on a.CitizenId=d.CitizenId", SQL.TABLE.ACCOUNTALARM, SQL.TABLE.ACCOUNTMEMBER, SQL.TABLE.MEMBER, SQL.TABLE.CITIZEN);
        public static string SheetList = string.Format("select a.ServiceSheetId,a.AccountId,a.ManagerId,b.RegRealName,b.RegMobile,f.CitizenId,f.Name olderName,c.AlarmTime,c.Type,c.Description,a.Status,a.CreateBy,a.CreateTime from {0} a left join {1} b on a.ManagerId=b.ManagerId left join {2} c on a.AlarmId=c.AlarmId inner join {3} d on c.AccountId=d.AccountId inner join {4} e on d.MemberId=e.MemberId left join {5} f on c.CitizenId=f.CitizenId", TABLE.SERVICESHEET, TABLE.PROPERTYMANAGER, TABLE.ACCOUNTALARM, TABLE.ACCOUNTMEMBER, TABLE.MEMBER, TABLE.CITIZEN);
        public static string CheckUnionId = string.Format("select a.MemberId,a.RealName,a.RegMobile,b.CanManagement from {0} a inner join {1} b on a.MemberId=b.MemberId", TABLE.MEMBER, TABLE.ACCOUNTMEMBER);
        public static string CheckUnionIdByCitizen = string.Format("select a.MemberId,c.CitizenId,c.Name,c.ContactMobile,b.CanManagement from {0} a inner join {1} b on a.MemberId=b.MemberId inner join {2} c on b.AccountId=c.AccountId", TABLE.MEMBER, TABLE.ACCOUNTMEMBER, TABLE.CITIZEN);
        public static string CheckUnionIdByMember = string.Format("select a.MemberId,b.AccountId,d.RealName,b.CanManagement from {0} a inner join {1} b on a.MemberId=b.MemberId left join {2} c on b.AccountId=c.AccountId inner join {3} d on c.MemberId=d.MemberId", TABLE.MEMBER, TABLE.ACCOUNTMEMBER, TABLE.ACCOUNTMEMBER,TABLE.MEMBER);
        public static string CheckUnionIdByDevice = string.Format("select a.MemberId,b.AccountId,c.DeviceSN ,b.CanManagement from {0} a inner join {1} b on a.MemberId=b.MemberId inner join {2} c on b.AccountId=c.AccountId", TABLE.MEMBER, TABLE.ACCOUNTMEMBER, TABLE.DEVICE);
        public static string BPSituation = string.Format("select a.MeasureTime,a.Sbp,a.Dbp from {0} a inner join {1} b on a.CitizenId=b.CitizenId inner join {2} c on b.AccountId=c.AccountId inner join {3} d on d.MemberId=c.MemberId", TABLE.BLOODPRESSURE, TABLE.CITIZEN, TABLE.ACCOUNTMEMBER, TABLE.MEMBER);
        public static string GLSituation = string.Format("select a.MeasureTime,a.MeasureValue,a.Meal from {0} a inner join {1} b on a.CitizenId=b.CitizenId inner join {2} c on b.AccountId=c.AccountId inner join {3} d on d.MemberId=c.MemberId", TABLE.GLYCEMIC, TABLE.CITIZEN, TABLE.ACCOUNTMEMBER, TABLE.MEMBER);
        public static string SASituation = string.Format("select a.MeasureTime from {0} a inner join {1} b on a.AccountId = b.AccountId inner join {2} c on b.MemberId = c.MemberId", TABLE.SMOKEALARM, TABLE.ACCOUNTMEMBER, TABLE.MEMBER);
        public static string DeviceDataList = string.Format("select a.DeviceId,a.DataNumber,a.AccountId,a.CitizenId,c.Name OldName,c.BirthdayYear,c.ContactMobile from {0} a inner join {1} b on a.AccountId=b.AccountId left join {2} c on a.CitizenId=c.CitizenId", TABLE.DEVICEDATA, TABLE.ACCOUNT, TABLE.CITIZEN);
    }
}
